package MEET.src.code;

import javax.swing.table.DefaultTableModel;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class UserDao {
    private static final String URL = "jdbc:mysql://sh-cdb-l52i6pgu.sql.tencentcdb.com:29750/schoolmeeting";
    private static final String USER = "root";
    private static final String PASSWORD = "745698Mjt123";
    private DefaultTableModel tableModel;

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void testConnection() {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            System.out.println("数据库连接成功！");
        } catch (SQLException e) {
            System.out.println("数据库连接失败：" + e.getMessage());
        }
    }

    public static boolean checkLogin(String uname, String pass) {
        String sql = "SELECT * FROM user WHERE ID = ? AND Upassword = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, uname);
            stmt.setString(2, pass);
            ResultSet rs = stmt.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            throw new RuntimeException("数据库操作失败", e);
        }
    }

    public static boolean registerUser(String ID, String username, String password, String age, String gender,
                                       String area, String identity, String dept, String power) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(
                     "INSERT INTO user (ID, Name, Age, Upassword, Sex, Uarea, Identity, Sdept, UPower) " +
                             "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
             )) {

            stmt.setString(1, ID);
            stmt.setString(2, username);
            stmt.setString(3, age);
            stmt.setString(4, password);
            stmt.setString(5, gender);
            stmt.setString(6, area);
            stmt.setString(7, identity);
            stmt.setString(8, dept);
            stmt.setString(9, power);

            int affectedRows = stmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static User getUserInfo(String userId) {
        String sql = "SELECT * FROM User WHERE ID = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, userId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return new User(
                        rs.getString("ID"),
                        rs.getString("Name"),
                        rs.getInt("Age"),
                        rs.getString("Upassword"),
                        rs.getString("Sex"),
                        rs.getString("Uarea"),
                        rs.getString("Identity"),
                        rs.getString("Sdept"),
                        rs.getString("UPower")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static boolean updateUser(User user) {
        String sql = "UPDATE User SET Name=?, Age=?, Sex=?, Uarea=?, Identity=?, Sdept=? WHERE ID=?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, user.getName());
            pstmt.setInt(2, user.getAge());
            pstmt.setString(3, user.getSex());
            pstmt.setString(4, user.getUarea());
            pstmt.setString(5, user.getIdentity());
            pstmt.setString(6, user.getSdept());
            pstmt.setString(7, user.getID());

            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static boolean updatePassword(String userId, String newPassword) {
        String sql = "UPDATE User SET Upassword=? WHERE ID=?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, newPassword);
            pstmt.setString(2, userId);

            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static List<Meeting> getUserReservations(String userId) {
        List<Meeting> reservations = new ArrayList<>();

        String userName = getUserNameById(userId);
        if (userName == null) {
            return reservations;
        }

        String sql = "SELECT * FROM Meeting WHERE Name = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, userName);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Meeting meeting = new Meeting();
                meeting.setMID(rs.getString("MID"));
                meeting.setMname(rs.getString("Mname"));
                meeting.setName(rs.getString("Name"));
                meeting.setMstarttime(rs.getTimestamp("Mstarttime"));
                meeting.setMendtime(rs.getTimestamp("Mendtime"));
                meeting.setPlace(rs.getString("Place"));
                meeting.setLocation(rs.getString("Location"));
                meeting.setRoom(rs.getString("Room"));
                meeting.setStatus(rs.getString("Status"));

                reservations.add(meeting);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return reservations;
    }

    private static String getUserNameById(String userId) {
        String sql = "SELECT Name FROM User WHERE ID = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, userId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getString("Name");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static String getUserPowerById(String userId) {
        String sql = "SELECT UPower FROM User WHERE ID = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, userId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getString("UPower");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static boolean cancelReservation(String meetingId) {
        String sql = "UPDATE Meeting SET Status = '已取消' WHERE MID = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, meetingId);
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static List<Meeting> loadMeetings(boolean hideExpired) {
        List<Meeting> meetings = new ArrayList<>();
        String sql = "SELECT * FROM Meeting " + (hideExpired ? "WHERE Mstarttime >= CURDATE()" : "");

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                meetings.add(new Meeting(
                        rs.getString("MID"),
                        rs.getString("Mname"),
                        rs.getString("Name"),
                        rs.getTimestamp("Mstarttime"),
                        rs.getTimestamp("Mendtime"),
                        rs.getString("Place"),
                        rs.getString("Location"),
                        rs.getString("Room"),
                        rs.getString("Form"),
                        rs.getString("Type"),
                        rs.getString("CID"),
                        rs.getString("ID"),
                        rs.getString("Status")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return meetings;
    }

    public static Meeting getMeetingDetails(String mid) {
        String sql = "SELECT * FROM Meeting WHERE MID = ?";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, mid);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return new Meeting(
                        rs.getString("MID"),
                        rs.getString("Mname"),
                        rs.getString("Name"),
                        rs.getTimestamp("Mstarttime"),
                        rs.getTimestamp("Mendtime"),
                        rs.getString("Place"),
                        rs.getString("Location"),
                        rs.getString("Room"),
                        rs.getString("Form"),
                        rs.getString("Type"),
                        rs.getString("CID"),
                        rs.getString("ID"),
                        rs.getString("Status")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static boolean updateMeeting(String mid, Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("UPDATE Meeting SET ");
        List<Object> values = new ArrayList<>();

        int count = 0;
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            if (count++ > 0) sql.append(", ");
            sql.append(entry.getKey()).append(" = ?");
            values.add(entry.getValue());
        }

        sql.append(" WHERE MID = ?");
        values.add(mid);

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

            for (int i = 0; i < values.size(); i++) {
                pstmt.setObject(i + 1, values.get(i));
            }

            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void cancelMeeting(String mid) {
        String sql = "UPDATE Meeting SET Status = '未通过' WHERE MID = ?";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, mid);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}